﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using DataServer.DTO;

namespace DataServer.DAO
{
    public class NhanVienDAO : DataProvider
    {
        public List<string> GetColName()
        {
            List<string> cols = new List<string>();
            string query = "SELECT * FROM NHANVIEN";
            DataTable dt = new DataTable();
            dt = executeQuery(query);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                cols.Add(dt.Columns[i].ToString());
            }
            return cols;
        }
        public List<NhanVienDTO> LayDanhSachNhanVien()
        {

            string query = "SELECT * FROM NHANVIEN ORDER BY maNV ASC";
            //adapter = new OleDbDataAdapter(query, connection);
            //DataTable dt = new DataTable();
            //adapter.Fill(dt);
            DataTable dt = executeQuery(query);
            List<NhanVienDTO> result = new List<NhanVienDTO>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NhanVienDTO nv = (NhanVienDTO)GetDataFromDataRow(dt, i);
                result.Add(nv);
            }

            return result;
        }

        public NhanVienDTO getNhanVien(int id)
        {
            string query = "SELECT * FROM NHANVIEN WHERE MaNV=" + id.ToString();
            DataTable dt = executeQuery(query);

            if (dt.Rows.Count == 0)
                return null;

            NhanVienDTO nv = (NhanVienDTO)this.GetDataFromDataRow(dt, 0);
            return nv;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            //DataRow dr = dt.Rows[i];
            NhanVienDTO nv = new NhanVienDTO();
            nv.MANV = (int)dt.Rows[i]["maNV"];
            nv.HoTenLot = (string)dt.Rows[i]["HoTenLot"];
            nv.TenNV = (string)dt.Rows[i]["TenNV"];
            nv.NgaySinh = (string)dt.Rows[i]["NgaySinh"];
            nv.DiaChi = (string)dt.Rows[i]["DiaChi"];
            nv.Phai = (string)dt.Rows[i]["Phai"];
            nv.Luong = (int)dt.Rows[i]["Luong"];
            nv.PHG = (string)dt.Rows[i]["PHG"];

            if (dt.Rows[i]["MA_NQL"] == null)
                nv.MA_NQL = -1;
            else
                nv.MA_NQL = (int)dt.Rows[i]["MA_NQL"];

            nv.SoThanNhan = (int)dt.Rows[i]["SoThanNhan"];
            nv.NgayNhanChuc = (string)dt.Rows[i]["NgayNhanChuc"];

            return nv;
        }

        public bool ThemNhanVien(NhanVienDTO nv)
        {

            string query = "INSERT INTO NHANVIEN(HoTenLot, TenNV, NgaySinh, DiaChi, Phai, Luong, PHG, MA_NQL, SoThanNhan, NgayNhanChuc) VALUES(N'"
                //+ nv.maNV + "','"
                + nv.HoTenLot + "',N'"
                + nv.TenNV + "','"
                + nv.NgaySinh + "',N'"
                + nv.DiaChi + "',N'"
                + nv.Phai + "','"
                + nv.Luong + "','"
                + nv.PHG + "','"
                + nv.MA_NQL + "','"
                + nv.SoThanNhan + "','"
                + nv.NgayNhanChuc + "')";
            try
            {
                executeNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                return false;
            }
        }

        public bool XoaNhanVien(int maNV)
        {
            string query = "DELETE FROM NHANVIEN WHERE maNV = '" + maNV.ToString() + "'";
            try
            {
                executeNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                return false;
            }
        }

        public bool SuaNhanVien(int id, NhanVienDTO nv)
        {
            string query = "UPDATE NHANVIEN SET HoTenLot = '" + nv.HoTenLot
                + "', TenNV = N'" + nv.TenNV
                + "', NgaySinh = '" + nv.NgaySinh
                + "', DiaChi = N'" + nv.DiaChi
                + "', Luong = N'" + nv.Luong
                + "', Phai = N'" + nv.Phai
                + "', PHG = '" + nv.PHG
                + "', MA_NQL ='" + nv.MA_NQL
                + "', SoThanNhan ='" + nv.SoThanNhan
                + "', NgayNhanChuc ='" + nv.NgayNhanChuc
                + "' WHERE maNV = '" + id + "'";

            try
            {
                executeNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                return false;
            }
        }
    }
}